import MySQLdb
import os, sys
import time
import math
import Queue, threading

import addrutil

class DBMgr:
  def __init__(self):
    self.conn = MySQLdb.connect(host="localhost", user="root", passwd="",
        db="hijacking", charset="utf8")
    self.cursor = self.conn.cursor()
    self.conn_time = time.time()
    self.eye_conn = MySQLdb.connect(host="localhost", user="root", passwd="",
        db="hijacking", charset="utf8")
    self.eye_cursor = self.eye_conn.cursor(MySQLdb.cursors.DictCursor)
    self.eye_conn_time = time.time()
    #
    self.dbQueue = Queue.Queue()
    self.dbThread = threading.Thread(target = self.dbWorker)
    self.dbThread.start()
    #
    self.MaxServer = 512
    self.MaxTime = 120
    self.AlarmTypeString = ("",
        "NewPrefix", "ShortPrefix", "MediaPrefix", "LongPrefix", "NewHomeAS",
        "NewHomeAS-NewPrefix", "NewHomeAS-ShortPrefix",
            "NewHomeAS-MediaPrefix", "NewHomeAS-LongPrefix",
        "BadPath-NewPrefix", "BadPath-ShortPrefix",
            "BadPath-MediaPrefix", "BadPath-LongPrefix",
        "BadPath-NewHomeAS-NewPrefix", "BadPath-NewHomeAS-ShortPrefix",
            "BadPath-NewHomeAS-MediaPrefix", "BadPath-NewHomeAS-LongPrefix", )
    self.probe_matrix = [None for i in xrange(self.MaxServer)]
    self.route_matrix= [None for i in xrange(self.MaxServer)]
    #self.probe_matrix = [
    #    [-1 for j in xrange(self.MaxServer)] for i in xrange(self.MaxTime)]
    #self.route_matrix = [
    #    [-1 for j in xrange(self.MaxServer)] for i in xrange(self.MaxTime)]
    self.probes = []
    self.routes = []
    self.bgpmons = []
    
  def dbWorker(self):
    #counter_in_queue = {'origin': 0, 'astuple_i': 0, 'astuple_u': 0,
    #          'alarm':  0, 'probe':   0, 'route': 0,
    #          'bgpmon': 0, 'signature': 0, }
    #total_in_queue = 0
    addrutil.log(int(time.time()), 0, "Y:dbWorker running!")
    while True:
      try:
        (type, param) = self.dbQueue.get(True)
        addrutil.log(int(time.time()), 0, "Y:db get type %s param %s" % (type, param))
        if time.time() - self.conn_time > 3600: # for one hour
          self.conn.ping(True)
          self.cursor = self.conn.cursor()
          self.conn_time = time.time()

        if type == "origin":
          #counter_in_queue['origin'] += 1
          pfx = param
          self.updateHomeas(pfx)
        elif type == "astuple_i":
          #counter_in_queue['astuple_i'] += 1
          # param:
          # (lastseen_v4, lastseen_v6, firstseen_v4, firstseen_v6, as0, as1, as2)
          sql = '''INSERT INTO astuple
              (lastseen_v4, lastseen_v6, firstseen_v4, firstseen_v6, as0, as1, as2)
              VALUES (%s, %s, %s, %s, %s, %s, %s)'''
          self.cursor.execute(sql, param)
        elif type == "astuple_u":
          #counter_in_queue['astuple_u'] += 1
          # param:
          # (lastseen_v4, lastseen_v6, firstseen_v4, firstseen_v6, as0, as1, as2)
          sql = '''UPDATE astuple
              SET lastseen_v4 = %s, lastseen_v6 = %s,
                  firstseen_v4 = %s, firstseen_v6 = %s
              WHERE as0 = %s AND as1 = %s AND as2 = %s'''
          self.cursor.execute(sql, param)
        elif type == "alarm":
          #counter_in_queue['alarm'] += 1
          pfx = param
          addrutil.log(int(time.time()), pfx.bgptime,
              "ALARM-%d %s id %d prefix %s %d %d bad_path %s" % (
              pfx.alarm_type, self.AlarmTypeString[pfx.alarm_type],
              pfx.alarm_id, pfx.dec, pfx.hole_ipbeg, pfx.hole_ipend, pfx.bad_path))
          self.addAlarm(pfx)
        elif type == "probe": # Buffered: 'probe', 'route', 'bgpmon', and 'signature'
          #counter_in_queue['probe'] += 1
          (alarm_id, ctime, srv_id, response, alarm_time) = param
          addrutil.log(int(time.time()), 0, "Y: probe get srv_id: %d ctime: %d atime: %d" % (srv_id, ctime, alarm_time))
          #addrutil.log(int(time.time()), alarm_time,
          #    "WHY? %r %r %r %r %r." % (
          #    alarm_id, ctime, srv_id, response, alarm_time))
          if ctime >= alarm_time and ctime - alarm_time < self.MaxTime:
            addrutil.log(int(time.time()), 0, "Y: srv_id_probe go.")
            if not self.probe_matrix[srv_id]:
              self.probe_matrix[srv_id] = [-1 for j in xrange(self.MaxTime)]
            self.probe_matrix[srv_id][ctime - alarm_time] = response
            self.probes.append("(%d, %d, %d, %d)" % (
              alarm_id, ctime, srv_id, response))
            if len(self.probes) > 126:
              self.flushProbe()
        elif type == "route":
          #counter_in_queue['route'] += 1
          (alarm_id, ctime, srv_id, isoldhome, alarm_time) = param
          addrutil.log(int(time.time()), 0, "Y: route get srv_id: %d ctime: %d atime: %d" % (srv_id, ctime, alarm_time))
          if ctime >= alarm_time and ctime - alarm_time < self.MaxTime:
            addrutil.log(int(time.time()), 0, "Y: srv_id_route go.")
            if not self.route_matrix[srv_id]:
              self.route_matrix[srv_id] = [-1 for j in xrange(self.MaxTime)]
            self.route_matrix[srv_id][ctime - alarm_time] = isoldhome
            self.routes.append("(%d, %d, %d, %d)" % (
              alarm_id, ctime, srv_id, isoldhome))
            if len(self.routes) > 126:
              self.flushRoute()
        elif type == "bgpmon":
          #counter_in_queue['bgpmon'] += 1
          (alarm_id, pfx) = param
          self.bgpmons.append("(%d, %d, '%s', %d, '%s')" % (
            alarm_id, pfx.bgptime, pfx.monip_dec, pfx.homeas, pfx.path_str))
          if len(self.bgpmons) > 126:
            self.flushBgpmon()
          del pfx.path_str # save memory
        elif type == "signature":
          #counter_in_queue['signature'] += 1
          (alarm_time, alarm_id) = param
          self.calculateSignature(alarm_id)
          addrutil.log(int(time.time()), alarm_time,
              "Signature saved, Qsize %d." % self.dbQueue.qsize())
      except Exception as e:
        addrutil.log(int(time.time()), 0, e)

  def updateReportCnt(self, alarm_id, hijack_type, is_ipv6):
    self.cursor.execute(
        'SELECT time, anomaly_type FROM alarm WHERE alarm_id = %d' % alarm_id)
    alarms = self.cursor.fetchall()
    if not alarms:
      return
    time = alarms[0][0]
    anomaly_type = alarms[0][1]
    cnt = [0, 0, 0, 1]
    cnt[anomaly_type] = 1
    #
    week = (time - 259200) / 604800
    self.cursor.execute('SELECT week FROM reportcnt WHERE week=%d' % week)
    reportcnts = self.cursor.fetchall()
    if not reportcnts:
      self.cursor.execute(
          'INSERT INTO reportcnt (week) VALUE (%d)' % week)
    #
    if hijack_type == 0: # stable hijacking
      if is_ipv6==1:
        self.cursor.execute(
          '''UPDATE reportcnt SET
             all_stable=all_stable+%d, all_stable_oa=all_stable_oa+%d,
             all_stable_aa=all_stable_aa+%d, all_stable_pa=all_stable_pa+%d,
             all_hijack=all_hijack+%d, all_hijack_oa=all_hijack_oa+%d,
             all_hijack_aa=all_hijack_aa+%d, all_hijack_pa=all_hijack_pa+%d,
             all_anomaly=all_anomaly+%d, all_anomaly_oa=all_anomaly_oa+%d,
             all_anomaly_aa=ipv6_anomaly_aa+%d, all_anomaly_pa=all_anomaly_pa+%d
             ipv6_stable=ipv6_stable+%d, ipv6_stable_oa=ipv6_stable_oa+%d,
             ipv6_stable_aa=ipv6_stable_aa+%d, ipv6_stable_pa=ipv6_stable_pa+%d,
             ipv6_hijack=ipv6_hijack+%d, ipv6_hijack_oa=ipv6_hijack_oa+%d,
             ipv6_hijack_aa=ipv6_hijack_aa+%d, ipv6_hijack_pa=ipv6_hijack_pa+%d,
             ipv6_anomaly=ipv6_anomaly+%d, ipv6_anomaly_oa=ipv6_anomaly_oa+%d,
             ipv6_anomaly_aa=ipv6_anomaly_aa+%d, ipv6_anomaly_pa=ipv6_anomaly_pa+%d
             WHERE week = %d''' % (
              cnt[3], cnt[0], cnt[1], cnt[2],
              cnt[3], cnt[0], cnt[1], cnt[2],
              cnt[3], cnt[0], cnt[1], cnt[2],
              cnt[3], cnt[0], cnt[1], cnt[2],
              cnt[3], cnt[0], cnt[1], cnt[2],
              cnt[3], cnt[0], cnt[1], cnt[2],
              week))
      else:
        self.cursor.execute(
          '''UPDATE reportcnt SET
             all_stable=all_stable+%d, all_stable_oa=all_stable_oa+%d,
             all_stable_aa=all_stable_aa+%d, all_stable_pa=all_stable_pa+%d,
             all_hijack=all_hijack+%d, all_hijack_oa=all_hijack_oa+%d,
             all_hijack_aa=all_hijack_aa+%d, all_hijack_pa=all_hijack_pa+%d,
             all_anomaly=all_anomaly+%d, all_anomaly_oa=all_anomaly_oa+%d,
             all_anomaly_aa=all_anomaly_aa+%d, all_anomaly_pa=all_anomaly_pa+%d,
             ipv4_stable=ipv4_stable+%d, ipv4_stable_oa=ipv4_stable_oa+%d,
             ipv4_stable_aa=ipv4_stable_aa+%d, ipv4_stable_pa=ipv4_stable_pa+%d,
             ipv4_hijack=ipv4_hijack+%d, ipv4_hijack_oa=ipv4_hijack_oa+%d,
             ipv4_hijack_aa=ipv4_hijack_aa+%d, ipv4_hijack_pa=ipv4_hijack_pa+%d,
             ipv4_anomaly=ipv4_anomaly+%d, ipv4_anomaly_oa=ipv4_anomaly_oa+%d,
             ipv4_anomaly_aa=ipv4_anomaly_aa+%d, ipv4_anomaly_pa=ipv4_anomaly_pa+%d
             WHERE week = %d''' % (
              cnt[3], cnt[0], cnt[1], cnt[2],
              cnt[3], cnt[0], cnt[1], cnt[2],
              cnt[3], cnt[0], cnt[1], cnt[2],
              cnt[3], cnt[0], cnt[1], cnt[2],
              cnt[3], cnt[0], cnt[1], cnt[2],
              cnt[3], cnt[0], cnt[1], cnt[2],
              week))
    elif hijack_type == 1: # suspicisou hijacking
      if is_ipv6==1:
        self.cursor.execute(
          '''UPDATE reportcnt SET
             all_hijack=all_hijack+%d, all_hijack_oa=all_hijack_oa+%d,
             all_hijack_aa=all_hijack_aa+%d, all_hijack_pa=all_hijack_pa+%d,
             all_anomaly=all_anomaly+%d, all_anomaly_oa=all_anomaly_oa+%d,
             all_anomaly_aa=all_anomaly_aa+%d, all_anomaly_pa=all_anomaly_pa+%d,
             ipv6_hijack=ipv6_hijack+%d, ipv6_hijack_oa=ipv6_hijack_oa+%d,
             ipv6_hijack_aa=ipv6_hijack_aa+%d, ipv6_hijack_pa=ipv6_hijack_pa+%d,
             ipv6_anomaly=ipv6_anomaly+%d, ipv6_anomaly_oa=ipv6_anomaly_oa+%d,
             ipv6_anomaly_aa=ipv6_anomaly_aa+%d, ipv6_anomaly_pa=ipv6_anomaly_pa+%d
             WHERE week = %d''' % (
              cnt[3], cnt[0], cnt[1], cnt[2],
              cnt[3], cnt[0], cnt[1], cnt[2],
              cnt[3], cnt[0], cnt[1], cnt[2],
              cnt[3], cnt[0], cnt[1], cnt[2],
              week))
      else:
        self.cursor.execute(
          '''UPDATE reportcnt SET
             all_hijack=all_hijack+%d, all_hijack_oa=all_hijack_oa+%d,
             all_hijack_aa=all_hijack_aa+%d, all_hijack_pa=all_hijack_pa+%d,
             all_anomaly=all_anomaly+%d, all_anomaly_oa=all_anomaly_oa+%d,
             all_anomaly_aa=all_anomaly_aa+%d, all_anomaly_pa=all_anomaly_pa+%d,
             ipv4_hijack=ipv4_hijack+%d, ipv4_hijack_oa=ipv4_hijack_oa+%d,
             ipv4_hijack_aa=ipv4_hijack_aa+%d, ipv4_hijack_pa=ipv4_hijack_pa+%d,
             ipv4_anomaly=ipv4_anomaly+%d, ipv4_anomaly_oa=ipv4_anomaly_oa+%d,
             ipv4_anomaly_aa=ipv4_anomaly_aa+%d, ipv4_anomaly_pa=ipv4_anomaly_pa+%d
             WHERE week = %d''' % (
              cnt[3], cnt[0], cnt[1], cnt[2],
              cnt[3], cnt[0], cnt[1], cnt[2],
              cnt[3], cnt[0], cnt[1], cnt[2],
              cnt[3], cnt[0], cnt[1], cnt[2],
              week))
    elif hijack_type == 2: # anomaly
      if is_ipv6==1:
        self.cursor.execute(
          '''UPDATE reportcnt SET
             all_anomaly=all_anomaly+%d, all_anomaly_oa=all_anomaly_oa+%d,
             all_anomaly_aa=all_anomaly_aa+%d, all_anomaly_pa=all_anomaly_pa+%d,
             ipv6_anomaly=ipv6_anomaly+%d, ipv6_anomaly_oa=ipv6_anomaly_oa+%d,
             ipv6_anomaly_aa=ipv6_anomaly_aa+%d, ipv6_anomaly_pa=ipv6_anomaly_pa+%d
             WHERE week = %d''' % (
              cnt[3], cnt[0], cnt[1], cnt[2],
              cnt[3], cnt[0], cnt[1], cnt[2],
              week))
      else:
        self.cursor.execute(
          '''UPDATE reportcnt SET
             all_anomaly=all_anomaly+%d, all_anomaly_oa=all_anomaly_oa+%d,
             all_anomaly_aa=all_anomaly_aa+%d, all_anomaly_pa=all_anomaly_pa+%d,
             ipv4_anomaly=ipv4_anomaly+%d, ipv4_anomaly_oa=ipv4_anomaly_oa+%d,
             ipv4_anomaly_aa=ipv4_anomaly_aa+%d, ipv4_anomaly_pa=ipv4_anomaly_pa+%d
             WHERE week = %d''' % (
              cnt[3], cnt[0], cnt[1], cnt[2],
              cnt[3], cnt[0], cnt[1], cnt[2],
              week))


  def calculateSignature(self, alarm_id):
    addrutil.log(int(time.time()), 0, "Y:calculate signature!")
    # flush probe, route
    self.flushProbe()
    self.flushRoute()
    self.flushBgpmon()
    for i in xrange(self.MaxServer):
      if self.probe_matrix[i]:
        addrutil.log(int(time.time()), 0, "Y:%d has-probe %s" % (i, self.probe_matrix[i]))
      if self.route_matrix[i]:
        addrutil.log(int(time.time()), 0, "Y:%d has-route %s" % (i, self.route_matrix[i]))
    srv_ids = [i for i in xrange(self.MaxServer)
        if self.probe_matrix[i] and self.route_matrix[i]]
    addrutil.log(int(time.time()), 0, "srv_ids:%s" % srv_ids)
    # prepare
    for i in srv_ids:
      presp = -1
      pisold = -1
      for j in xrange(0, self.MaxTime):
        if self.probe_matrix[i][j] == -1:
          self.probe_matrix[i][j] = presp
        if self.route_matrix[i][j] == -1:
          self.route_matrix[i][j] = pisold
        presp = self.probe_matrix[i][j]
        pisold = self.route_matrix[i][j]
    # coff
    counter = [
        [[0 for k in xrange(2)] for j in xrange(2)]
          for i in xrange(self.MaxTime)
      ]
    coff = [0.0 for j in xrange(self.MaxTime)]
    for j in xrange(0, self.MaxTime):
      sum_xy = sum_x = sum_y = sum_xx = sum_yy = 0.
      cnt = 0
      for i in srv_ids:
        addrutil.log(int(time.time()), 0, "Y:time-%d, index-%d, probe-%d, route-%d" % (j, i, self.probe_matrix[i][j], self.route_matrix[i][j]))
        if self.probe_matrix[i][j] == -1 or self.route_matrix[i][j] == -1:
          continue
        counter[j][ self.route_matrix[i][j] ][ self.probe_matrix[i][j] ] += 1
        sum_xy += self.probe_matrix[i][j] * self.route_matrix[i][j]
        sum_xx += self.probe_matrix[i][j] * self.probe_matrix[i][j]
        sum_yy += self.route_matrix[i][j] * self.route_matrix[i][j]
        sum_x += self.probe_matrix[i][j]
        sum_y += self.route_matrix[i][j]
        cnt += 1
      if cnt == 0:
        continue
      coff[j] = math.sqrt(
          (sum_xx / cnt - (sum_x * sum_x) / (cnt * cnt)) *
          (sum_yy / cnt - (sum_y * sum_y) / (cnt * cnt)))
      if math.fabs(coff[j]) < 1E-10:
        coff[j] = 0.
      else:
        coff[j] = ((sum_xy / cnt) - (sum_x * sum_y) / (cnt * cnt)) / coff[j]
    # insert
    sigs = []
    nsig6 = max_nsig6 = 0
    for i in xrange(0, self.MaxTime):
      if coff[i] != 0:
        addrutil.log(int(time.time()), 0, "Y:time-%d, coff-%d" % (i, coff[i]))
      if coff[i] >= 0.6:
        nsig6 += 1
      else: # only consider continues alarm 2012.5.29
        if max_nsig6 < nsig6:
          max_nsig6 = nsig6
        nsig6 = 0
      tag = 0;
      if i > 0:
        if math.fabs(coff[i] - coff[i - 1]) > 1E-10:
          tag = 1
        if tag == 0:
          for a in xrange(0, 2):
            for b in xrange(0, 2):
              if counter[i][a][b] != counter[i - 1][a][b]:
                tag = 1
      else:
        if (counter[i][0][0] or counter[i][0][1] or
            counter[i][1][0] or counter[i][1][1]):
          tag = 1
      if tag == 1:
        sigs.append("(%d, %d, %g, %d, %d, %d, %d)" % (
          alarm_id, i, coff[i],
          counter[i][0][0], counter[i][0][1],
          counter[i][1][0], counter[i][1][1]))
    if max_nsig6 < nsig6:
      max_nsig6 = nsig6
    # is a hijacking
    addrutil.log(int(time.time()), 0, "Y:max_nsig6:%d" % max_nsig6)
    if max_nsig6 >= 10:
      self.cursor.execute(
        'UPDATE alarm SET is_hijacking = "Y" WHERE alarm_id = %s', (alarm_id, )
      )
      sql = '''SELECT alarm_id, time, prefix_dec,
          homeas, oldhomeas, bad_path, is_ipv6
          FROM alarm WHERE alarm_id = %s'''
      self.cursor.execute(sql, (alarm_id, ))
      alarm_info = self.cursor.fetchone()
      if alarm_info:
        if alarm_info[3] != 0:
          os.system('./sendtwitter.py %d %s %s %s %s 0 &' % (
            alarm_id, alarm_info[1], alarm_info[2],
            alarm_info[3], alarm_info[4]))
          os.system('./sendemail.py %d %s %s %s %s 0 &' % (
            alarm_id, alarm_info[1], alarm_info[2],
            alarm_info[3], alarm_info[4]))
        else:
          os.system('./sendtwitter.py %d %s %s %s %s "%s" &' % (
            alarm_id, alarm_info[1], alarm_info[2],
            alarm_info[3], alarm_info[4], alarm_info[5]))
          os.system('./sendemail.py %d %s %s %s %s "%s" &' % (
            alarm_id, alarm_info[1], alarm_info[2],
            alarm_info[3], alarm_info[4], alarm_info[5]))
        # HTTP POST to subscribers
        subscribers = ' '.join(self.getSubscribers())
        os.system('./sendsubscriber.py %d %s %s %s %s "%s" "%s" &' % (
          alarm_id, alarm_info[1], alarm_info[2],
          alarm_info[3], alarm_info[4], alarm_info[5], subscribers))
      self.updateReportCnt(alarm_id, 0, int(alarm_info[6]))
    elif max_nsig6 > 0:
      self.cursor.execute(
        'UPDATE alarm SET is_hijacking = "6" WHERE alarm_id = %s', (alarm_id, )
      )
      sql = '''SELECT alarm_id, time, prefix_dec,
          homeas, oldhomeas, bad_path, is_ipv6
          FROM alarm WHERE alarm_id = %s'''
      self.cursor.execute(sql, (alarm_id, ))
      alarm_info = self.cursor.fetchone()
      self.updateReportCnt(alarm_id, 1, int(alarm_info[6]))
    else:
      sql = '''SELECT alarm_id, time, prefix_dec,
          homeas, oldhomeas, bad_path, is_ipv6
          FROM alarm WHERE alarm_id = %s'''
      self.cursor.execute(sql, (alarm_id, ))
      alarm_info = self.cursor.fetchone()
      self.updateReportCnt(alarm_id, 2, int(alarm_info[6]))
    if len(sigs) > 0:
      sql = '''INSERT INTO signature (
          alarm_id, elapse_time, sig, cnt00, cnt01, cnt10, cnt11)
          VALUES ''' + ', '.join(sigs)
      self.cursor.execute(sql)
    # reset
    self.probe_matrix = [None for i in xrange(self.MaxServer)]
    self.route_matrix= [None for i in xrange(self.MaxServer)]
    #self.probe_matrix = [
    #    [-1 for j in xrange(self.MaxServer)] for i in xrange(self.MaxTime)]
    #self.route_matrix = [
    #    [-1 for j in xrange(self.MaxServer)] for i in xrange(self.MaxTime)]

  def get_rpki_result(self, is_ipv6, pfx_length, ipbeg_bin, ipend_bin, homeas):
    sql = ('''SELECT asn, max_length FROM rpki
        WHERE is_ipv6 = %s AND ipbeg <= %s AND ipend >= %s''')
    param = (is_ipv6, ipbeg_bin, ipend_bin, )
    self.cursor.execute(sql, param)
    rpkis = self.cursor.fetchall()
    rpki_res = ''
    if rpkis:
      #rpki_res= 'y'
      for rp in rpkis:
        if homeas == rp[0]:
          if pfx_length <= rp[1]:
            rpki_res = 'N' # this is DEFINITELY NOT a hijacking
            break
          else:
            rpki_res = 'Y'
        elif pfx_length <= rp[1]:
          if rpki_res == '':
            rpki_res = 'M'
    return rpki_res
  
  def addAlarm(self, pfx):
    if ' ' in pfx.bad_path:
      sql = ('''INSERT INTO alarm (
          alarm_id, time, type, is_ipv6,
          prefix_dec, pfx_length, ipbeg, ipend,
          homeas, oldhomeas, bad_path, rpki, n_pfx, update_id, anomaly_type)
          VALUES (%s, %s, %s, %s,
              %s, %s, %s, %s,
              0, 0, %s, '', %s, %s, %s)''')
      param = (pfx.alarm_id, pfx.bgptime, pfx.alarm_type, pfx.is_ipv6,
          pfx.dec, pfx.length, pfx.ipbeg_bin, pfx.ipend_bin,
          pfx.bad_path, pfx.n_pfx, pfx.update_id, pfx.bad_path.count(' '))
      self.cursor.execute(sql, param)
    else:
      # get old homeas
      sql = ('''SELECT homeas FROM origin
          WHERE ipbeg = %s AND pfx_length = %s AND homeas != %s
          ORDER BY uptime DESC LIMIT 1''')
      param = (pfx.ipbeg_bin, pfx.length, pfx.homeas, )
      self.cursor.execute(sql, param)
      oldhomeRow = self.cursor.fetchone()
      oldhome = 0
      if oldhomeRow:
        oldhome = oldhomeRow[0]
      else:
        for ipbeg_bin, psize in addrutil.get_father_prefixes(
            pfx.ipbeg_bin, pfx.length):
          sql = ('''SELECT homeas FROM origin
              WHERE ipbeg = %s AND pfx_length = %s AND homeas != %s
              ORDER BY uptime DESC LIMIT 1''')
          param = (ipbeg_bin, psize, pfx.homeas, )
          self.cursor.execute(sql, param)
          oldhomeRow = self.cursor.fetchone()
          if oldhomeRow:
            oldhome = oldhomeRow[0]
            break
      # get rpki result
      rpki_res = self.get_rpki_result(pfx.is_ipv6, pfx.length,
          pfx.ipbeg_bin, pfx.ipend_bin, pfx.homeas)
      # insert alarm
      sql = ('''INSERT INTO alarm (
          alarm_id, time, type, is_ipv6,
          prefix_dec, pfx_length, ipbeg, ipend,
          homeas, oldhomeas, bad_path, rpki, n_pfx, update_id, anomaly_type)
          VALUES (%s, %s, %s, %s,
              %s, %s, %s, %s,
              %s, %s, '', %s, %s, %s, 0)''')
      param = (pfx.alarm_id, pfx.bgptime, pfx.alarm_type, pfx.is_ipv6,
          pfx.dec, pfx.length, pfx.ipbeg_bin, pfx.ipend_bin,
          pfx.homeas, oldhome, rpki_res, pfx.n_pfx, pfx.update_id)
      self.cursor.execute(sql, param)
    
  def maintain_updateAlarmRpki(self):
    sql = (
        "SELECT alarm_id, is_ipv6, pfx_length, ipbeg, ipend, homeas FROM alarm"
      )
    self.cursor.execute(sql)
    alarms = self.cursor.fetchall()
    cnt = 0
    for am in alarms:
      if am[5] == 0:
        continue
      # get RPKI entries
      rpki_res = self.get_rpki_result(am[1], am[2], am[3], am[4], am[5])
      # update rpki
      sql = 'UPDATE alarm SET rpki = %s WHERE alarm_id = %s'
      self.cursor.execute(sql, (rpki_res, am[0]))
      cnt += 1
      if cnt % 1000 == 0:
        print >> sys.stderr, "%d done." % cnt
    print "All done"

  
  def flushBgpmon(self):
    if len(self.bgpmons) > 0:
      sql = (
          'INSERT INTO bgpmon (alarm_id, time, monip_dec, homeas, path) VALUES '
          + ', '.join(self.bgpmons) )
      self.cursor.execute(sql)
      del self.bgpmons[:]
    
  def flushProbe(self):
    if len(self.probes) > 0:
      sql = ( 'INSERT INTO probe (alarm_id, time, srv_id, response) VALUES '
          + ', '.join(self.probes) )
      self.cursor.execute(sql)
      del self.probes[:]
    
  def flushRoute(self):
    if len(self.routes) > 0:
      sql = ''' INSERT INTO route (alarm_id, time, srv_id, isoldhome)
          VALUES ''' + ', '.join(self.routes)
      self.cursor.execute(sql)
      del self.routes[:]
  
  def updateHomeas(self, pfx):
    if pfx.homeas == 0: # withdraw
      # update previous home
      sql = ('''UPDATE origin
          SET uptime = %s - lastseen + uptime, lastseen = %s, isup = 0
          WHERE ipbeg = %s AND pfx_length = %s AND monip = %s AND isup = 1''')
      param = (pfx.bgptime, pfx.bgptime,
          pfx.ipbeg_bin, pfx.length, pfx.monip_bin)
      self.cursor.execute(sql, param)
      return
    # fetch ipbeg-ipend-monitor -> origin_id-homeas-isup
    sql = ('''SELECT origin_id, homeas, isup FROM origin
        WHERE ipbeg = %s AND pfx_length = %s AND monip = %s''')
    param = (pfx.ipbeg_bin, pfx.length, pfx.monip_bin)
    self.cursor.execute(sql, param)
    currRow = self.cursor.fetchall()
    origID = 0
    if currRow:
      for cr in currRow:
        if cr[2] == 1: # update previous home
          if cr[1] == pfx.homeas:
            return # same home AS
          else:
            sql = ('''UPDATE origin
                SET uptime = %s - lastseen + uptime, lastseen = %s, isup = 0
                WHERE origin_id = %s''')
            param = (pfx.bgptime, pfx.bgptime, cr[0])
            self.cursor.execute(sql, param)
        if cr[1] == pfx.homeas:
          origID = cr[0] 
    # update current home
    if origID: # existing homeas
      sql = ('''UPDATE origin
          SET isup = 1, monas = %s, lastseen = %s
          WHERE origin_id = %s''')
      param = (pfx.monas, pfx.bgptime, origID)
      self.cursor.execute(sql, param)
    else: # new homeas
      sql = ('''INSERT INTO origin
          (is_ipv6, prefix_dec, pfx_length, ipbeg, ipend,
           monip_dec, monip, monas, homeas,
           firstseen, lastseen, uptime, isup)
          VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 0, 1)''')
      param = (
          pfx.is_ipv6, pfx.dec, pfx.length, pfx.ipbeg_bin, pfx.ipend_bin,
          pfx.monip_dec, pfx.monip_bin, pfx.monas, pfx.homeas,
          pfx.bgptime, pfx.bgptime)
      self.cursor.execute(sql, param)
      
  def getMaxAlarmID(self):
    sql = "SELECT MAX(alarm_id) FROM alarm"
    self.cursor.execute(sql)
    idrow = self.cursor.fetchone()
    if not idrow:
      return 0
    else:
      return idrow[0]

  def getMaxUpdateID(self):
    sql = "SELECT MAX(update_id) FROM alarm"
    self.cursor.execute(sql)
    idrow = self.cursor.fetchone()
    if not idrow:
      return 0
    else:
      return idrow[0]

  def getSubscribers(self):
    sql = "SELECT url FROM subscriber"
    self.cursor.execute(sql)
    return self.cursor.fetchall()

  def loadAllHomeAS(self): # init function
    ssconn = MySQLdb.connect(host="localhost", user="root", passwd="",
        db="hijacking", charset="utf8")   
    sscursor = ssconn.cursor(MySQLdb.cursors.SSCursor)
    sql = ''' SELECT ipbeg, pfx_length, homeas, is_ipv6, monip, isup
            FROM origin '''
    sscursor.execute(sql)
    for r in sscursor:
      yield r
    sscursor.close()
    ssconn.close()
    
  def loadAllAStuple(self): # init function
    ssconn = MySQLdb.connect(host="localhost", user="root", passwd="",
        db="hijacking", charset="utf8")  
    sscursor = ssconn.cursor(MySQLdb.cursors.SSCursor)
    sql = '''SELECT as0, as1, as2,
        lastseen_v4, lastseen_v6, firstseen_v4, firstseen_v6
        FROM astuple'''
    sscursor.execute(sql)
    for r in sscursor:
      yield r
    sscursor.close()
    ssconn.close()

  def get_eye(self, is_ipv6=False, set_counter=False, number=90, each_eye=5):
    if time.time() - self.eye_conn_time > 3600: # for one hour
      self.eye_conn.ping(True)
      self.eye_cursor = self.eye_conn.cursor(MySQLdb.cursors.DictCursor)
      self.eye_conn_time = time.time()
    asn_eyes = {}
    if not is_ipv6:
      sql = ''' SELECT * FROM eye
          WHERE ping4 != '' AND bgp4 != '' AND status = 1
          ORDER BY counter '''
      if not set_counter: # TODO for debug
        sql = ''' SELECT * FROM eye
            WHERE ping4 != '' AND bgp4 != '' '''
    else:
      sql = ''' SELECT * FROM eye
          WHERE ping6 != '' AND bgp6 != '' AND status = 1
          ORDER BY counter '''
      if not set_counter: # TODO for debug
        sql = ''' SELECT * FROM eye
            WHERE ping6 != '' AND bgp6 != '' '''
    self.eye_cursor.execute(sql)
    if set_counter:
      all_eyes = self.eye_cursor.fetchall()
      eyes = []
      for e in all_eyes:
        a = e.get('asn')
        aes = asn_eyes.get(a, None)
        if aes and len(aes) >= each_eye:
          continue
        if not aes:
          aes = set()
          asn_eyes[a] = aes
        eid = e.get('eye_id')
        aes.add(eid)
        eyes.append(e)
        if len(eyes) >= number:
          break
      eids = [('%d' % e.get('eye_id')) for e in eyes]
      sql = ''' UPDATE eye SET counter = counter + 1
          WHERE eye_id IN (''' + ', '.join(eids) + ')'
      self.eye_cursor.execute(sql)
    else:
      eyes = self.eye_cursor.fetchall()
    return eyes

  def update_failed_eye_counter(self, feid, fcid):
    if time.time() - self.eye_conn_time > 3600: # for one hour
      self.eye_conn.ping(True)
      self.eye_cursor = self.eye_conn.cursor(MySQLdb.cursors.DictCursor)
      self.eye_conn_time = time.time()
    addrutil.log(int(time.time()), 0,
        "faild eyes(%d, %d)." % (len(feid), len(fcid)))
    addrutil.log(int(time.time()), 0, "Y:timeout: %s" % feid)
    addrutil.log(int(time.time()), 0, "Y:noresult: %s" % fcid)
    if feid:
      sql = ''' UPDATE eye SET counter = counter + 30
          WHERE eye_id IN (''' + ', '.join(feid) + ')'
      self.eye_cursor.execute(sql)
    if fcid:
      sql = ''' UPDATE eye SET counter = counter + 30
          WHERE counter_id IN (''' + ', '.join(fcid) + ')'
      self.eye_cursor.execute(sql)

  def add_eye(self, type, addr, user, pwd, ping4, ping6, ping_timeout,
      bgp4, bgp6, parse_ping, parse_bgp, bgp4ip=0, bgp6ip=0, contact='', asn=0):
    sql = ''' INSERT INTO eye (type, addr, user, pwd, ping4, ping6,
          ping_timeout, bgp4, bgp6, parse_ping, parse_bgp, bgp4ip,
          bgp6ip, contact, asn)
        VALUES (%s, %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s, %s,
                %s, %s, %s) '''
    self.eye_cursor.execute(sql, (
      type, addr, user, pwd, ping4, ping6,
      ping_timeout, bgp4, bgp6, parse_ping, parse_bgp, bgp4ip,
      bgp6ip, contact, asn))

  #def loadCurrentHomeAS(self):
  #  sql = "select prefix_dec, monip, homeas from origin where isup = 1"
  #  self.cursor.execute(sql)
  #  return self.cursor.fetchall()

  def clearOutdatedHomeAS(self, curtime): # init function
    #sql = "SELECT MAX(lastseen) FROM origin"
    #self.cursor.execute(sql)
    #mxt = self.cursor.fetchone()
    #if mxt is None or len(mxt) < 1 or mxt[0] >= int(curtime):
    #  return 0
    #
    sql = "DELETE FROM origin WHERE lastseen < %s AND isup = 0 LIMIT 200000"
    param = ((int(curtime) - 3600 * 24 * 60), ) # two months
    np = self.cursor.execute(sql, param)
    sql = ''' DELETE FROM origin
        WHERE lastseen < %s AND uptime < 3600 LIMIT 200000 '''
    param = ((int(curtime) - 3600 * 24 * 120), ) # four months
    np += self.cursor.execute(sql, param)
    return np

  def clearOutdatedASTuple(self, curtime): # init function
    #sql = "SELECT MAX(lastseen) FROM astuple"
    #self.cursor.execute(sql)
    #mxt = self.cursor.fetchone()
    #if mxt is None or len(mxt) < 1 or mxt[0] >= int(curtime):
    #  return 0
    #
    # Update v4/v6 expired
    #  AS pair
    param = ((int(curtime) - 3600 * 24 * 120), ) # four months
    sql = '''UPDATE astuple SET lastseen_v4 = 0, firstseen_v4 = 0
        WHERE as0 = 0 AND lastseen_v4 < %s LIMIT 5000'''
    self.cursor.execute(sql, param)
    sql = '''UPDATE astuple SET lastseen_v6 = 0, firstseen_v6 = 0
        WHERE as0 = 0 AND lastseen_v6 < %s LIMIT 5000'''
    self.cursor.execute(sql, param)
    #  AS triple
    param = ((int(curtime) - 3600 * 24 * 180), ) # six months
    sql = '''UPDATE astuple SET lastseen_v4 = 0, firstseen_v4 = 0
        WHERE as0 != 0 AND lastseen_v4 < %s LIMIT 5000'''
    self.cursor.execute(sql, param)
    sql = '''UPDATE astuple SET lastseen_v6 = 0, firstseen_v6 = 0
        WHERE as0 != 0 AND lastseen_v6 < %s LIMIT 5000'''
    self.cursor.execute(sql, param)
    # Delete v4/v6 both expired
    sql = 'DELETE FROM astuple WHERE lastseen_v4 = 0 AND lastseen_v6 = 0'
    return self.cursor.execute(sql)

  def tmp_gen_update_id_in_alarm(self):
    sql = '''SELECT alarm_id, homeas, bad_path FROM alarm'''
    #ssconn = MySQLdb.connect(
    #    host="localhost", user="root", passwd="", db="hijacking", charset="utf8")   
    #sscursor = ssconn.cursor(MySQLdb.cursors.SSCursor)
    #sscursor.execute(sql)
    self.cursor.execute(sql)
    alarms = self.cursor.fetchall()
    update_id = 0
    path = ''
    monip_dec = ''
    n_over = 0
    #for a in sscursor:
    for a in alarms:
      self.cursor.execute('SELECT monip_dec, path FROM bgpmon WHERE alarm_id = %d' % a[0])
      rows = self.cursor.fetchall()
      if rows:
        r = rows[0]
        if r[0] != monip_dec or r[1] != path:
          monip_dec = r[0]
          path = r[1]
          update_id += 1
      else:
        this_path = '%d %s' % (a[1], a[2])
        if this_path != path:
          monip_dec = ''
          path = this_path
          update_id += 1
      self.cursor.execute('UPDATE alarm SET update_id = %d WHERE alarm_id = %d' % (update_id, a[0]))
      n_over += 1
      if n_over % 10000 == 0:
        print >> sys.stderr, "%d over", n_over

  def tmp_gen_reportcnt(self):
    sql = '''SELECT alarm_id, is_hijacking, is_ipv6 FROM alarm WHERE EXISTS (
               SELECT * FROM signature WHERE
                 signature.alarm_id = alarm.alarm_id)'''
    self.cursor.execute(sql)
    alarms = self.cursor.fetchall()
    n_over = 0
    for a in alarms:
      if a[1] == 'Y':
        if a[2]==1:
          self.updateReportCnt(a[0], 0, 1)
        else:
          self.updateReportCnt(a[0], 0, 0)
      elif a[1] != '':
        if a[2]==1:
          self.updateReportCnt(a[0], 1, 1)
        else:
          self.updateReportCnt(a[0], 1, 0)
      else:
        if a[2]==1:
          self.updateReportCnt(a[0], 2, 1)
        else:
          self.updateReportCnt(a[0], 2, 0)
      n_over += 1
      if n_over % 10000 == 0:
        print >> sys.stderr, "%d over", n_over

  def tmp_update_anomaly_type_in_alarm(self):
    ssconn = MySQLdb.connect(
        host="localhost", user="root", passwd="", db="hijacking", charset="utf8")   
    sscursor = ssconn.cursor(MySQLdb.cursors.SSCursor)
    sql = '''UPDATE alarm set anomaly_type = 0 WHERE homeas != 0'''
    sscursor.execute(sql)
    sql = '''UPDATE alarm set anomaly_type = 1 WHERE homeas = 0 AND bad_path LIKE '% %' '''
    sscursor.execute(sql)
    sql = '''UPDATE alarm set anomaly_type = 2 WHERE homeas = 0 AND bad_path LIKE '% % %' '''
    sscursor.execute(sql)
    addrutil.log(int(time.time()), 0, "All done!")

  def close(self):
    self.cursor.close()
    self.conn.close()
    self.eye_cursor.close()
    self.eye_conn.close()

if __name__ == '__main__':
  db = DBMgr()
  #db.tmp_checkAlarmPrefix()
  #db.tmp_updateAlarmOldhome()
  #db.tmp_updateAlarmIsHijacking(357543, 99999999)
  #db.tmp_migrate_origin_table_to_v6()
  #db.tmp_migrate_alarm_table_to_v6()
  #db.tmp_migrate_bgpmon_table_to_v6()
  #db.tmp_update_anomaly_type_in_alarm()
  #db.tmp_gen_update_id_in_alarm()
  db.tmp_gen_reportcnt()
  #db.maintain_updateAlarmRpki()
  #db.tmp_recalculateSignature(290626, 290742+1)
  #db.get_eye(False, True)
  db.close()
  sys.exit()
